﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using HolcimProject.Common.DTO;
using HolcimProject.Common;
using SPTraceLogger;
namespace HolcimProject.Function
{
    public class FunctionFinding
    {
        /// <summary>
        /// Get all data from list FindingAuditName
        /// </summary>
        /// <returns></returns>
        public List<ObjectDTO> GetAllAuditName()
        {

            try
            {
                List<ObjectDTO> lst = new List<ObjectDTO>();
                using (SPSite site = new SPSite(Constants.Site_Url))
                {
                    Microsoft.SharePoint.SPList ls = ls = site.OpenWeb().Lists[Constants.List_FindingAuditName];
                    lst = (from SPListItem Item in ls.Items
                           select new ObjectDTO
                           {
                               ID = Item.ID,
                               Title = Item.Title

                           }).ToList();

                }
                return lst;
            }
            catch
            {
                throw;
            }

        }

        /// <summary>
        /// Get all data from list FindingSystem
        /// </summary>
        /// <returns></returns>
        public List<ObjectDTO> GetAllSystem()
        {
            try
            {
                List<ObjectDTO> lst = new List<ObjectDTO>();
                using (SPSite site = new SPSite(Constants.Site_Url))
                {
                    Microsoft.SharePoint.SPList ls = ls = site.OpenWeb().Lists[Constants.List_FindingSystem];
                    lst = (from SPListItem Item in ls.Items
                           select new ObjectDTO
                           {
                               ID = Item.ID,
                               Title = Item.Title

                           }).GroupBy(p => p.Title).Select(g => g.First()).ToList();

                }
                return lst;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// get all data from list FindingClause
        /// </summary>
        /// <returns></returns>
        public List<ObjectDTO> GetAllClause(string system)
        {
            try
            {
                List<ObjectDTO> lst = new List<ObjectDTO>();
                using (SPSite site = new SPSite(Constants.Site_Url))
                {
                    Microsoft.SharePoint.SPList ls = ls = site.OpenWeb().Lists[Constants.List_FindingSystem];
                    lst = (from SPListItem Item in ls.Items
                           where Item[Constants.Field_FindingSystem_System] == null ? false : Item[Constants.Field_FindingSystem_System].ToString() == system
                           select new ObjectDTO
                           {
                               ID = Item.ID,
                               Title = Item[Constants.Field_FindingSystem_Clause] == null ? "" : Item[Constants.Field_FindingSystem_Clause].ToString()

                           }).GroupBy(p => p.Title).Select(g => g.First()).ToList();

                }
                return lst;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Get all site name from List FindingDepartment
        /// </summary>
        /// <returns></returns>
        public List<ObjectDTO> GetAllSite()
        {
            try
            {
                List<ObjectDTO> lst = new List<ObjectDTO>();
                using (SPSite site = new SPSite(Constants.Site_Url))
                {
                    Microsoft.SharePoint.SPList ls = ls = site.OpenWeb().Lists[Constants.List_FindingDepartment];
                    lst = (from SPListItem Item in ls.Items
                           select new ObjectDTO
                           {
                               ID = Item.ID,
                               Title = Item[Constants.Field_FindingDepartment_Site] == null ? "" : Item[Constants.Field_FindingDepartment_Site].ToString()

                           }).GroupBy(p => p.Title).Select(g => g.First()).ToList();

                }
                return lst;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Get all department from list FindingDeparment
        /// </summary>
        /// <param name="siteName"></param>
        /// <returns></returns>
        public List<ObjectDTO> GetAllDeparment(string siteName)
        {
            try
            {
                List<ObjectDTO> lst = new List<ObjectDTO>();
                using (SPSite site = new SPSite(Constants.Site_Url))
                {
                    Microsoft.SharePoint.SPList ls = ls = site.OpenWeb().Lists[Constants.List_FindingDepartment];
                    lst = (from SPListItem Item in ls.Items
                           where Item[Constants.Field_FindingDepartment_Site] == null ? false : Item[Constants.Field_FindingDepartment_Site].ToString() == siteName
                           select new ObjectDTO
                           {
                               ID = Item.ID,
                               Title = Item[Constants.Field_FindingDepartment_Department] == null ? "" : Item[Constants.Field_FindingDepartment_Department].ToString()

                           }).GroupBy(p => p.Title).Select(g => g.First()).ToList();

                }
                return lst;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Get all audit department from list FindingDepartment
        /// </summary>
        /// <param name="departmentName"></param>
        /// <returns></returns>
        public List<ObjectDTO> GetAllAuditDepartment(string departmentName)
        {
            try
            {
                List<ObjectDTO> lst = new List<ObjectDTO>();
                using (SPSite site = new SPSite(Constants.Site_Url))
                {
                    Microsoft.SharePoint.SPList ls = ls = site.OpenWeb().Lists[Constants.List_FindingDepartment];
                    lst = (from SPListItem Item in ls.Items
                           where Item[Constants.Field_FindingDepartment_Department] == null ? false : Item[Constants.Field_FindingDepartment_Department].ToString() == departmentName
                           select new ObjectDTO
                           {
                               ID = Item.ID,
                               Title = Item[Constants.Field_FindingDepartment_AuditDepartment] == null ? "" : Item[Constants.Field_FindingDepartment_AuditDepartment].ToString()

                           }).GroupBy(p => p.Title).Select(g => g.First()).ToList();

                }
                return lst;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Get all classification from list FindingClassification
        /// </summary>
        /// <returns></returns>
        public List<ObjectDTO> GetAllClassification()
        {
            try
            {
                List<ObjectDTO> lst = new List<ObjectDTO>();
                using (SPSite site = new SPSite(Constants.Site_Url))
                {
                    Microsoft.SharePoint.SPList ls = ls = site.OpenWeb().Lists[Constants.List_FindingClassification];
                    lst = (from SPListItem Item in ls.Items
                           select new ObjectDTO
                           {
                               ID = Item.ID,
                               Title = Item.Title

                           }).ToList();

                }
                return lst;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Update finding
        /// </summary>
        /// <param name="finding"></param>
        /// <returns></returns>
        public FindingDTO UpdateFinding(FindingDTO finding)
        {
            try
            {
                using (SPSite spSite = new SPSite(Constants.Site_Url))
                {
                    Microsoft.SharePoint.SPList spList = spSite.OpenWeb().Lists[Constants.List_Finding];
                    SPListItem item = null;
                    // if id==0-> Add new items; id>0 -> edit item; else fail
                    if (finding.Id == 0)
                    {
                        item = spList.AddItem();
                    }
                    else if (finding.Id > 0)
                    {
                        item = spList.GetItemById(finding.Id);
                    }
                    else
                    {
                        return null;
                    }

                    // update value
                    spList.ParentWeb.AllowUnsafeUpdates = true;
                    item[Constants.Field_Finding_AuditName] = finding.AuditName;
                    item[Constants.Field_Finding_System] = finding.System;
                    item[Constants.Field_Finding_Clause] = finding.Clause;
                    item[Constants.Field_Finding_Site] = finding.Site;
                    item[Constants.Field_Finding_Department] = finding.Department;
                    item[Constants.Field_Finding_AuditeeDepartment] = finding.AuditeeDepartment;
                    item[Constants.Field_Finding_Classification] = finding.Classification;
                    item[Constants.Field_Finding_LeadAuditor] = finding.LeadAuditor;
                    item[Constants.Field_Finding_Auditee] = finding.Auditee;
                    item[Constants.Field_Finding_Finding] = finding.Finding;

                    item.Update();
                    spList.ParentWeb.AllowUnsafeUpdates = false;

                    return new FindingDTO
                    {
                        Id = item.ID
                    };
                }
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// Update Task
        /// </summary>
        /// <param name="findingTask"></param>
        /// <returns></returns>
        public bool UpdateTask(FindingTaskDTO findingTask)
        {
            try
            {
                using (SPSite spSite = new SPSite(Constants.Site_Url))
                {
                    Microsoft.SharePoint.SPList spList = spSite.OpenWeb().Lists[Constants.List_FindingTask];
                    SPListItem item = null;
                    // if id==0-> Add new items; id>0 -> edit item; else fail
                    if (findingTask.Id == 0)
                    {
                        item = spList.AddItem();
                    }
                    else if (findingTask.Id > 0)
                    {
                        item = spList.GetItemById(findingTask.Id);
                    }
                    else
                    {
                        return false;
                    }
                    spList.ParentWeb.AllowUnsafeUpdates = true;
                    item[Constants.Field_FindingTasks_Finding] = findingTask.IdFinding;
                    item[Constants.Field_FindingTasks_Owner] = findingTask.Owner;
                    item[Constants.Field_FindingTasks_Role] = findingTask.Role;
                    item[Constants.Field_FindingTasks_State] = findingTask.State;
                    item[Constants.Field_FindingTasks_Status] = findingTask.Status;
                    item[Constants.Field_FindingTasks_Comment] = findingTask.Comment;
                    item[Constants.Field_FindingTasks_Flat] = findingTask.Flat;
                    if (findingTask.AttackFileName != null)
                    {
                        item.Attachments.Add(findingTask.AttackFileName, findingTask.AttackFile);
                    }
                    item.Update();
                    spList.ParentWeb.AllowUnsafeUpdates = false;
                    return true;
                }
            }
            catch
            {
                throw;
            }

        }

        /// <summary>
        /// Get all task of FindingID
        /// </summary>
        /// <param name="findingID">ID of finding</param>
        /// <returns></returns>
        public List<FindingTaskDTO> GetAllTaskOfFindingByID(int findingID)
        {
            try
            {
                List<FindingTaskDTO> lst = new List<FindingTaskDTO>();
                using (SPSite site = new SPSite(Constants.Site_Url))
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                        {
                            // get all task of findingID and had Flat==done
                            Microsoft.SharePoint.SPList ls = ls = site.OpenWeb().Lists[Constants.List_FindingTask];
                            lst = (from SPListItem Item in ls.Items
                                   //  where Item[Constants.Field_FindingTasks_Finding] == null ? false : Item[Constants.Field_FindingTasks_Finding].ToString().Split(';')[0] == findingID.ToString() && Item[Constants.Field_FindingTasks_Flat] == null ? false : Item[Constants.Field_FindingTasks_Flat].ToString() == "done"
                                   where Item[Constants.Field_FindingTasks_Finding].ToString().Split(';')[0] == findingID.ToString() && Item[Constants.Field_FindingTasks_Flat] != null
                                   select new FindingTaskDTO
                                   {
                                       Id = Item.ID,
                                       Owner = Item[Constants.Field_FindingTasks_Owner] == null ? "" : Item[Constants.Field_FindingTasks_Owner].ToString(),
                                       Role = Item[Constants.Field_FindingTasks_Role] == null ? "" : Item[Constants.Field_FindingTasks_Role].ToString(),
                                       State = Item[Constants.Field_FindingTasks_State] == null ? "" : Item[Constants.Field_FindingTasks_State].ToString(),
                                       Status = Item[Constants.Field_FindingTasks_Status] == null ? "" : Item[Constants.Field_FindingTasks_Status].ToString(),
                                       Comment = Item[Constants.Field_FindingTasks_Comment] == null ? "" : Item[Constants.Field_FindingTasks_Comment].ToString(),
                                       AttackFileUrl = Item.Attachments.Count == 0 ? "" : Item.Attachments.UrlPrefix + Item.Attachments[0].ToString(),
                                       DateLastModify = Convert.ToDateTime(Item[Constants.Field_FindingTasks_DateLastEdit])
                                   }).ToList();
                        });

                }
                lst.Sort(delegate(FindingTaskDTO p1, FindingTaskDTO p2)
                    {
                        return p1.DateLastModify.CompareTo(p2.DateLastModify);
                    }
                );
                return lst;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        ///  Get all complete date in task by findingID
        /// </summary>
        /// <param name="findingID"></param>
        /// <returns></returns>
        public string GetAllCompleteDateFindingTaskByFindingID(int findingID)
        {
            try
            {
                string strCompleteDate = "";
                SPSecurity.RunWithElevatedPrivileges(delegate()
                        {
                            string strQuery = @"   <Where>
                                          <Eq>
                                             <FieldRef Name='Finding' />
                                             <Value Type='Lookup'>" + findingID + @"</Value>
                                          </Eq>
                                       </Where>";
                            SPListItemCollection items = Utilities.GetDataInSharePointList(Constants.Site_Url, Constants.List_FindingTask, strQuery);
                            if (items != null)
                            {
                                foreach (SPListItem item in items)
                                {
                                    if (item[Constants.Field_FindingTasks_CompleteDate] != null)
                                    {
                                        strCompleteDate += Convert.ToDateTime(item[Constants.Field_FindingTasks_CompleteDate].ToString()).ToString("MM/dd/yyyy") + "</br>";
                                    }
                                }
                            }
                        });
                return strCompleteDate;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Get finding by ID
        /// </summary>
        /// <param name="findingID">id of finding</param>
        /// <returns></returns>
        public FindingDTO GetFindingByID(int findingID, string state)
        {
            try
            {
                FindingDTO finding = new FindingDTO();
                using (SPSite spSite = new SPSite(Constants.Site_Url))
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                        {
                            Microsoft.SharePoint.SPList spList = spSite.OpenWeb().Lists[Constants.List_Finding];
                            SPListItem item = spList.GetItemById(findingID);
                            if (item != null)
                            {
                                finding.Id = item.ID;
                                finding.AuditName = item[Constants.Field_Finding_AuditName] == null ? "" : item[Constants.Field_Finding_AuditName].ToString();
                                finding.System = item[Constants.Field_Finding_System] == null ? "" : item[Constants.Field_Finding_System].ToString();
                                finding.Clause = item[Constants.Field_Finding_Clause] == null ? "" : item[Constants.Field_Finding_Clause].ToString();
                                finding.Site = item[Constants.Field_Finding_Site] == null ? "" : item[Constants.Field_Finding_Site].ToString();
                                finding.Department = item[Constants.Field_Finding_Department] == null ? "" : item[Constants.Field_Finding_Department].ToString();
                                finding.AuditeeDepartment = item[Constants.Field_Finding_AuditeeDepartment] == null ? "" : item[Constants.Field_Finding_AuditeeDepartment].ToString();
                                finding.System = item[Constants.Field_Finding_System] == null ? "" : item[Constants.Field_Finding_System].ToString();
                                finding.Classification = item[Constants.Field_Finding_Classification] == null ? "" : item[Constants.Field_Finding_Classification].ToString();
                                finding.LeadAuditor = item[Constants.Field_Finding_LeadAuditor] == null ? "" : item[Constants.Field_Finding_LeadAuditor].ToString();
                                finding.Auditee = item[Constants.Field_Finding_Auditee] == null ? "" : item[Constants.Field_Finding_Auditee].ToString();
                                finding.Finding = item[Constants.Field_Finding_Finding] == null ? "" : item[Constants.Field_Finding_Finding].ToString();
                                finding.Auditor = item[Constants.Field_Finding_CreateBy].ToString();
                                finding.Correction = item[Constants.Field_Finding_Correction] == null ? "" : item[Constants.Field_Finding_Correction].ToString();
                                finding.RootCause = item[Constants.Field_Finding_RootCause] == null ? "" : item[Constants.Field_Finding_RootCause].ToString();
                                finding.Corrective = item[Constants.Field_Finding_Corrective] == null ? "" : item[Constants.Field_Finding_Corrective].ToString();
                                finding.Evidence = item[Constants.Field_Finding_Evidence] == null ? "" : item[Constants.Field_Finding_Evidence].ToString();
                                finding.PersonInChage = item[Constants.Field_Finding_PersonInChage] == null ? "" : item[Constants.Field_Finding_PersonInChage].ToString();
                                if (item[Constants.Field_Finding_CompleteDate] != null)
                                {
                                    finding.CompleteDate = Convert.ToDateTime(item[Constants.Field_Finding_CompleteDate].ToString());
                                }
                                if (item[Constants.Field_Finding_CompleteDate2] != null)
                                {
                                    finding.CompleteDate2 = Convert.ToDateTime(item[Constants.Field_Finding_CompleteDate2].ToString());
                                }
                                if (state == Constants.State_ApproveEvidence_Auditor)
                                {
                                    finding.LstCompleteDate = GetAllCompleteDateFindingTaskByFindingID(item.ID);
                                }

                            }
                        });
                }
                return finding;
            }
            catch
            {
                throw;
            }

        }

        /// <summary>
        ///  Update action log
        /// </summary>
        /// <param name="finding"></param>
        /// <returns></returns>
        public bool UpdateActionLog(FindingDTO finding)
        {
            try
            {
                using (SPSite spSite = new SPSite(Constants.Site_Url))
                {
                    Microsoft.SharePoint.SPList spList = spSite.OpenWeb().Lists[Constants.List_Finding];
                    SPListItem item = null;
                    item = spList.GetItemById(finding.Id);

                    // update value
                    spList.ParentWeb.AllowUnsafeUpdates = true;
                    item[Constants.Field_Finding_Correction] = finding.Correction;
                    item[Constants.Field_Finding_RootCause] = finding.RootCause;
                    item[Constants.Field_Finding_Corrective] = finding.Corrective;
                    item[Constants.Field_Finding_CompleteDate] = finding.CompleteDate;
                    item[Constants.Field_Finding_PersonInChage] = finding.PersonInChage;

                    item.Update();
                    spList.ParentWeb.AllowUnsafeUpdates = false;
                    return true;
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        ///  Update comment for task
        /// </summary>
        /// <param name="findingTask"></param>
        /// <returns></returns>
        public bool UpdateCommentTask(FindingTaskDTO findingTask)
        {
            try
            {
                using (SPSite spSite = new SPSite(Constants.Site_Url))
                {
                    Microsoft.SharePoint.SPList spList = spSite.OpenWeb().Lists[Constants.List_FindingTask];
                    SPListItem item = null;
                    item = spList.GetItemById(findingTask.Id);

                    spList.ParentWeb.AllowUnsafeUpdates = true;
                    item[Constants.Field_FindingTasks_Comment] = findingTask.Comment;
                    item[Constants.Field_FindingTasks_Flat] = findingTask.Flat;
                    if (findingTask.Status != "")
                    {
                        item[Constants.Field_FindingTasks_Status] = findingTask.Status;
                    }
                    if (findingTask.AttackFileName != null)
                    {
                        item.Attachments.Add(findingTask.AttackFileName, findingTask.AttackFile);
                    }
                    if (findingTask.CompleteDate != DateTime.MinValue)
                    {
                        item[Constants.Field_FindingTasks_CompleteDate] = findingTask.CompleteDate;
                    }
                    item.Update();
                    spList.ParentWeb.AllowUnsafeUpdates = false;
                    return true;
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Update evidence
        /// </summary>
        /// <param name="finding"></param>
        /// <returns></returns>
        public bool UpdateEvidence(FindingDTO finding)
        {
            using (SPSite spSite = new SPSite(Constants.Site_Url))
            {
                Microsoft.SharePoint.SPList spList = spSite.OpenWeb().Lists[Constants.List_Finding];
                SPListItem item = null;
                item = spList.GetItemById(finding.Id);

                // update value
                spList.ParentWeb.AllowUnsafeUpdates = true;
                item[Constants.Field_Finding_Evidence] = finding.Evidence;

                item.Update();
                spList.ParentWeb.AllowUnsafeUpdates = false;
                return true;
            }
        }

        /// <summary>
        /// Update completeDate2 for Finding
        /// </summary>
        /// <param name="finding"></param>
        /// <returns></returns>
        public bool UpdateCompleteDate2(FindingDTO finding)
        {
            using (SPSite spSite = new SPSite(Constants.Site_Url))
            {
                Microsoft.SharePoint.SPList spList = spSite.OpenWeb().Lists[Constants.List_Finding];
                SPListItem item = null;
                item = spList.GetItemById(finding.Id);

                // update value
                spList.ParentWeb.AllowUnsafeUpdates = true;
                if (finding.CompleteDate2 != null)
                {
                    if (finding.CompleteDate2 != DateTime.MinValue)
                    {
                        item[Constants.Field_Finding_CompleteDate2] = finding.CompleteDate2;
                        item.Update();
                    }
                }
                spList.ParentWeb.AllowUnsafeUpdates = false;
                return true;
            }
        }

        /// <summary>
        /// Get all finding(s)
        /// </summary>
        /// <returns></returns>
        public List<FindingDTO> GetFinding()
        {
            try
            {
                List<FindingDTO> lst = new List<FindingDTO>();
                using (SPSite site = new SPSite(Constants.Site_Url))
                {
                    // get all task of findingID and had Flat==done
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                        {
                            Microsoft.SharePoint.SPList ls = ls = site.OpenWeb().Lists[Constants.List_Finding];
                            lst = (from SPListItem Item in ls.Items
                                   select new FindingDTO
                                   {
                                       Id = Item.ID,
                                       Clause = Item[Constants.Field_Finding_Clause] == null ? "" : Item[Constants.Field_Finding_Clause].ToString(),
                                       AuditeeDepartment = Item[Constants.Field_Finding_AuditeeDepartment] == null ? "" : Item[Constants.Field_Finding_AuditeeDepartment].ToString(),
                                       System = Item[Constants.Field_Finding_System] == null ? "" : Item[Constants.Field_Finding_System].ToString(),
                                       Classification = Item[Constants.Field_Finding_Classification] == null ? "" : Item[Constants.Field_Finding_Classification].ToString(),
                                       LeadAuditor = Item[Constants.Field_Finding_LeadAuditor] == null ? "" : Item[Constants.Field_Finding_LeadAuditor].ToString(),
                                       Auditee = Item[Constants.Field_Finding_Auditee] == null ? "" : Item[Constants.Field_Finding_Auditee].ToString(),
                                       Finding = Item[Constants.Field_Finding_Finding] == null ? "" : Item[Constants.Field_Finding_Finding].ToString(),
                                       Correction = Item[Constants.Field_Finding_Correction] == null ? "" : Item[Constants.Field_Finding_Correction].ToString(),
                                       RootCause = Item[Constants.Field_Finding_RootCause] == null ? "" : Item[Constants.Field_Finding_RootCause].ToString(),
                                       Corrective = Item[Constants.Field_Finding_Corrective] == null ? "" : Item[Constants.Field_Finding_Corrective].ToString(),                                      
                                       PersonInChage = Item[Constants.Field_Finding_PersonInChage] == null ? "" : Item[Constants.Field_Finding_PersonInChage].ToString(),
                                       Auditor = Item[Constants.Field_Finding_CreateBy] == null ? "" : Item[Constants.Field_Finding_CreateBy].ToString(),
                                       CompleteDate = Item[Constants.Field_Finding_CompleteDate] == null ? DateTime.MinValue : Convert.ToDateTime(Item[Constants.Field_Finding_CompleteDate]),
                                       LstCompleteDate = GetAllCompleteDateFindingTaskByFindingID(Item.ID)

                                   }).ToList();
                        });
                    if (lst != null)
                    {
                        for (int i = 0; i < lst.Count; i++)
                        {
                            FindingTaskDTO findingTask = GetLastStateForFindingByID(lst[i].Id);
                            if (findingTask != null)
                            {
                                lst[i].IdFindingTask = findingTask.Id;
                                lst[i].StateFindingTask = findingTask.State;
                            }

                        }
                    }

                }

                return lst;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Get finding task current state
        /// </summary>
        /// <returns></returns>
        public List<FindingDTO> GetFindingTaskCurrentState()
        {
            try
            {
                List<FindingDTO> lst = new List<FindingDTO>();
                using (SPSite site = new SPSite(Constants.Site_Url))
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                        {
                            // get all task of findingID and had Flat==done
                            Microsoft.SharePoint.SPList ls = ls = site.OpenWeb().Lists[Constants.List_Finding];
                            lst = (from SPListItem Item in ls.Items
                                   select new FindingDTO
                                   {
                                       Id = Item.ID,
                                       Clause = Item[Constants.Field_Finding_Clause] == null ? "" : Item[Constants.Field_Finding_Clause].ToString(),
                                       Finding = Item[Constants.Field_Finding_Finding] == null ? "" : Item[Constants.Field_Finding_Finding].ToString(),
                                       Correction = Item[Constants.Field_Finding_Correction] == null ? "" : Item[Constants.Field_Finding_Correction].ToString(),
                                       RootCause = Item[Constants.Field_Finding_RootCause] == null ? "" : Item[Constants.Field_Finding_RootCause].ToString(),
                                       Corrective = Item[Constants.Field_Finding_Corrective] == null ? "" : Item[Constants.Field_Finding_Corrective].ToString(),
                                       CompleteDate = Item[Constants.Field_Finding_CompleteDate] == null ? DateTime.MinValue : Convert.ToDateTime(Item[Constants.Field_Finding_CompleteDate]),
                                       LstCompleteDate = GetAllCompleteDateFindingTaskByFindingID(Item.ID)

                                   }).ToList();
                        });
                    if (lst != null)
                    {
                        for (int i = 0; i < lst.Count; i++)
                        {
                            FindingTaskDTO findingTask = GetLastStateForFindingByID(lst[i].Id);
                            if (findingTask != null)
                            {
                                lst[i].IdFindingTask = findingTask.Id;
                                lst[i].StateFindingTask = findingTask.State;
                            }

                        }
                    }
                }

                return lst;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Get finding task by me
        /// </summary>
        /// <returns></returns>
        public List<FindingDTO> GetFindingTaskByMe()
        {
            try
            {
                List<FindingDTO> lst = new List<FindingDTO>();
                List<FindingDTO> lstTmp = new List<FindingDTO>();
                // get current user
                SPUser currentUser = Utilities.GetCurrentUser(Constants.Site_Url);
                // Check user have ISO Admin
                bool isISOAdmin = false;
                isISOAdmin = Utilities.CheckUserExitsInGroup(Constants.Site_Url, Constants.Group_ISOAdmin, currentUser.LoginName);

                // get all finding
                using (SPSite site = new SPSite(Constants.Site_Url))
                {
                    // get all task of findingID and had Flat==done
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                        {
                            Microsoft.SharePoint.SPList ls = ls = site.OpenWeb().Lists[Constants.List_Finding];
                            lst = (from SPListItem Item in ls.Items
                                   select new FindingDTO
                                   {
                                       Id = Item.ID,
                                       Clause = Item[Constants.Field_Finding_Clause] == null ? "" : Item[Constants.Field_Finding_Clause].ToString(),
                                       Finding = Item[Constants.Field_Finding_Finding] == null ? "" : Item[Constants.Field_Finding_Finding].ToString(),
                                       Correction = Item[Constants.Field_Finding_Correction] == null ? "" : Item[Constants.Field_Finding_Correction].ToString(),
                                       RootCause = Item[Constants.Field_Finding_RootCause] == null ? "" : Item[Constants.Field_Finding_RootCause].ToString(),
                                       Corrective = Item[Constants.Field_Finding_Corrective] == null ? "" : Item[Constants.Field_Finding_Corrective].ToString(),
                                       LeadAuditor = Item[Constants.Field_Finding_LeadAuditor] == null ? "" : Item[Constants.Field_Finding_LeadAuditor].ToString().Split('#')[1],
                                       Auditee = Item[Constants.Field_Finding_Auditee] == null ? "" : Item[Constants.Field_Finding_Auditee].ToString().Split('#')[1],
                                       PersonInChage = Item[Constants.Field_Finding_PersonInChage] == null ? "" : Item[Constants.Field_Finding_PersonInChage].ToString().Split('#')[1],
                                       Auditor = Item[Constants.Field_Finding_CreateBy] == null ? "" : Item[Constants.Field_Finding_CreateBy].ToString().Split('#')[1],
                                       CompleteDate = Item[Constants.Field_Finding_CompleteDate] == null ? DateTime.MinValue : Convert.ToDateTime(Item[Constants.Field_Finding_CompleteDate]),
                                       LstCompleteDate = GetAllCompleteDateFindingTaskByFindingID(Item.ID)
                                   }).ToList();
                        });
                    //end

                    // Update current state
                    if (lst != null)
                    {
                        for (int i = 0; i < lst.Count; i++)
                        {
                            FindingTaskDTO findingTask = GetLastStateForFindingByID(lst[i].Id);
                            if (findingTask != null)
                            {
                                lst[i].IdFindingTask = findingTask.Id;
                                lst[i].StateFindingTask = findingTask.State;
                            }

                        }
                    }
                    //end 

                    // Filter finding task by me
                    if (lst != null)
                    {
                        foreach (FindingDTO findingDTO in lst)
                        {
                            if (findingDTO.StateFindingTask != Constants.Status_Finish)
                            {
                                switch (findingDTO.StateFindingTask)
                                {
                                    case Constants.State_EditActionLog:
                                        if (findingDTO.Auditee.ToUpper() == currentUser.LoginName.ToUpper())
                                        {
                                            lstTmp.Add(findingDTO);
                                        }
                                        break;
                                    case Constants.State_ReviewActionLog:
                                        if (findingDTO.PersonInChage.ToUpper() == currentUser.LoginName.ToUpper())
                                        {
                                            lstTmp.Add(findingDTO);
                                        }
                                        break;
                                    case Constants.State_ApprovalActionLog_Auditor:
                                        if (findingDTO.Auditor.ToUpper() == currentUser.LoginName.ToUpper())
                                        {
                                            lstTmp.Add(findingDTO);
                                        }
                                        break;
                                    case Constants.State_ApprovalActionLog_LeadAuditor:
                                        if (findingDTO.LeadAuditor.ToUpper() == currentUser.LoginName.ToUpper())
                                        {
                                            lstTmp.Add(findingDTO);
                                        }
                                        break;
                                    case Constants.State_UpdateEvidence:
                                        if (findingDTO.PersonInChage.ToUpper() == currentUser.LoginName.ToUpper())
                                        {
                                            lstTmp.Add(findingDTO);
                                        }
                                        break;
                                    case Constants.State_ApproveEvidence_Auditor:
                                        if (findingDTO.Auditor.ToUpper() == currentUser.LoginName.ToUpper())
                                        {
                                            lstTmp.Add(findingDTO);
                                        }
                                        break;
                                    case Constants.State_ApproveEvidence_ISOAdmin:
                                        if (isISOAdmin)
                                        {
                                            lstTmp.Add(findingDTO);
                                        }
                                        break;
                                    case Constants.State_ApproveEvidence_ISOAdmin_Finish:
                                        if (isISOAdmin)
                                        {
                                            lstTmp.Add(findingDTO);
                                        }
                                        break;

                                }
                            }
                        }
                    }
                    //end

                }

                return lstTmp;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Get finding for me
        /// </summary>
        /// <returns></returns>
        public List<FindingDTO> GetFindingForMe()
        {
            try
            {
                List<FindingDTO> lst = new List<FindingDTO>();
                List<FindingDTO> lstTmp = new List<FindingDTO>();
                // get current user
                SPUser currentUser = Utilities.GetCurrentUser(Constants.Site_Url);
                // Check user have ISO Admin
                bool isISOAdmin = false;
                isISOAdmin = Utilities.CheckUserExitsInGroup(Constants.Site_Url, Constants.Group_ISOAdmin, currentUser.LoginName);

                // get all finding
                using (SPSite site = new SPSite(Constants.Site_Url))
                {
                    // get all task of findingID and had Flat==done
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                    {
                        string query = String.Format(@"<Where>
                                              <Or>
                                                 <Eq>
                                                    <FieldRef Name='Author' />
                                                    <Value Type='User'>{0}</Value>
                                                 </Eq>
                                                 <Or>
                                                    <Eq>
                                                       <FieldRef Name='Lead_x0020_Auditor' />
                                                       <Value Type='UserMulti'>{1}</Value>
                                                    </Eq>
                                                    <Or>
                                                       <Eq>
                                                          <FieldRef Name='Auditee' />
                                                          <Value Type='UserMulti'>{2}</Value>
                                                       </Eq>
                                                       <Eq>
                                                          <FieldRef Name='Person_x0020_In_x0020_Charge' />
                                                          <Value Type='User'>{3}</Value>
                                                       </Eq>
                                                    </Or>
                                                 </Or>
                                              </Or>
                                           </Where>", currentUser.LoginName, currentUser.LoginName, currentUser.LoginName, currentUser.LoginName);
                        SPListItemCollection items = Utilities.GetDataInSharePointList(Constants.Site_Url, Constants.List_Finding, query);
                        lst = (from SPListItem Item in items
                               select new FindingDTO
                               {
                                   Id = Item.ID,
                                   Clause = Item[Constants.Field_Finding_Clause] == null ? "" : Item[Constants.Field_Finding_Clause].ToString(),
                                   AuditeeDepartment = Item[Constants.Field_Finding_AuditeeDepartment] == null ? "" : Item[Constants.Field_Finding_AuditeeDepartment].ToString(),
                                   System = Item[Constants.Field_Finding_System] == null ? "" : Item[Constants.Field_Finding_System].ToString(),
                                   Classification = Item[Constants.Field_Finding_Classification] == null ? "" : Item[Constants.Field_Finding_Classification].ToString(),
                                   LeadAuditor = Item[Constants.Field_Finding_LeadAuditor] == null ? "" : Item[Constants.Field_Finding_LeadAuditor].ToString(),
                                   Auditee = Item[Constants.Field_Finding_Auditee] == null ? "" : Item[Constants.Field_Finding_Auditee].ToString(),
                                   Finding = Item[Constants.Field_Finding_Finding] == null ? "" : Item[Constants.Field_Finding_Finding].ToString(),
                                   Correction = Item[Constants.Field_Finding_Correction] == null ? "" : Item[Constants.Field_Finding_Correction].ToString(),
                                   RootCause = Item[Constants.Field_Finding_RootCause] == null ? "" : Item[Constants.Field_Finding_RootCause].ToString(),
                                   Corrective = Item[Constants.Field_Finding_Corrective] == null ? "" : Item[Constants.Field_Finding_Corrective].ToString(),
                                   PersonInChage = Item[Constants.Field_Finding_PersonInChage] == null ? "" : Item[Constants.Field_Finding_PersonInChage].ToString(),
                                   Auditor = Item[Constants.Field_Finding_CreateBy] == null ? "" : Item[Constants.Field_Finding_CreateBy].ToString(),
                                   CompleteDate = Item[Constants.Field_Finding_CompleteDate] == null ? DateTime.MinValue : Convert.ToDateTime(Item[Constants.Field_Finding_CompleteDate]),
                                   LstCompleteDate = GetAllCompleteDateFindingTaskByFindingID(Item.ID)
                               }).ToList();
                    });
                    //end
                    if (lst != null)
                    {
                        for (int i = 0; i < lst.Count; i++)
                        {
                            FindingTaskDTO findingTask = GetLastStateForFindingByID(lst[i].Id);
                            if (findingTask != null)
                            {
                                lst[i].IdFindingTask = findingTask.Id;
                                lst[i].StateFindingTask = findingTask.State;
                            }

                        }
                    }
                }

                return lst;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Get current state for finding
        /// </summary>
        /// <param name="findingID"></param>
        /// <returns></returns>
        public FindingTaskDTO GetLastStateForFindingByID(int findingID)
        {
            try
            {
                List<FindingTaskDTO> lst = new List<FindingTaskDTO>();
                using (SPSite site = new SPSite(Constants.Site_Url))
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                        {
                            // get all task of findingID and had Flat==done
                            Microsoft.SharePoint.SPList ls = ls = site.OpenWeb().Lists[Constants.List_FindingTask];
                            lst = (from SPListItem Item in ls.Items
                                   // where Item[Constants.Field_FindingTasks_Finding] == null ? false : Item[Constants.Field_FindingTasks_Finding].ToString().Split(';')[0] == findingID.ToString() && Item[Constants.Field_FindingTasks_Flat] == null ? true : Item[Constants.Field_FindingTasks_Flat].ToString() != "done"
                                   where Item[Constants.Field_FindingTasks_Finding].ToString().Split(';')[0] == findingID.ToString() && Item[Constants.Field_FindingTasks_Flat] == null
                                   select new FindingTaskDTO
                                   {
                                       Id = Item.ID,
                                       State = Item[Constants.Field_FindingTasks_State] == null ? "" : Item[Constants.Field_FindingTasks_State].ToString(),
                                       DateLastModify = Convert.ToDateTime(Item[Constants.Field_FindingTasks_DateLastEdit])
                                   }).ToList();
                        });
                }
                lst.Sort(delegate(FindingTaskDTO p1, FindingTaskDTO p2)
                {
                    return p1.DateLastModify.CompareTo(p2.DateLastModify);
                }
                );
                if (lst != null)
                {
                    if (lst.Count != 0)
                    {
                        return lst.Last();
                    }
                }
                return null;
            }
            catch
            {
                throw;
            }
        }


        public ParameterDTO GetParameterByFindTaskID(int findingTaskID)
        {
            try
            {
                ParameterDTO parameter = new ParameterDTO();
                using (SPSite site = new SPSite(Constants.Site_Url))
                {
                    SPSecurity.RunWithElevatedPrivileges(delegate()
                        {
                            SPListItem item = site.OpenWeb().Lists[Constants.List_FindingTask].GetItemById(findingTaskID);
                            if (item != null)
                            {
                                parameter.FindingTaskID = findingTaskID;
                                parameter.FindingID = item[Constants.Field_FindingTasks_Finding] == null ? 0 : Convert.ToInt32(item[Constants.Field_FindingTasks_Finding].ToString().Split(';')[0]);
                                parameter.State = item[Constants.Field_FindingTasks_State] == null ? "" : item[Constants.Field_FindingTasks_State].ToString();
                            }
                        });
                }

                return parameter;
            }
            catch (Exception ex)
            {
                SPLoggingService.WriteULSError("Finding", Categories.WebPart, ex);
                return null;
            }
        }
    }
}
